package com.itheima.mapper;

import com.github.pagehelper.Page;
import com.itheima.pojo.EmpGender;
import com.itheima.pojo.StuClazz;
import com.itheima.pojo.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudentMapper {
    /**
     * 学员列表查询
     */
    Page<Student> page(String name, Integer degree, Integer clazzId);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into student (name, no, gender, phone, id_card, is_college, address, degree, graduation_date, clazz_id," +
            "                    create_time, update_time)" +
            "values (#{name},#{no},#{gender},#{phone},#{idCard},#{isCollege},#{address},#{degree},#{graduationDate},#{clazzId},#{createTime},#{updateTime})")
    void insertStudent(Student student);

    /**
     * 根据ID查询学生
     */
    @Select("select * from student where id = #{id}")
    Student selectStuById(Integer id);

    /**
     * 修改学员
     */
    @Update("UPDATE student" +
            " SET name            = #{name}," +
            "    no              = #{no}," +
            "    gender          = #{gender}," +
            "    phone           = #{phone}," +
            "    id_card         = #{idCard}," +
            "    is_college      = #{isCollege}," +
            "    address         = #{address}," +
            "    degree          = #{degree}," +
            "    graduation_date = #{graduationDate}," +
            "    clazz_id        = #{clazzId}," +
            "    violation_count = #{violationCount}," +
            "    violation_score = #{violationScore}," +
            "    create_time     = #{createTime}," +
            "    update_time     = #{updateTime}" +
            " WHERE id = #{id}")
    void updateStudent(Student student);

    /**
     * 删除学生信息
     */
    void deleteStu(List<Integer> ids);

    /**
     * 违纪处理
     */
    @Update("update student set violation_score=violation_score+ #{score},violation_count=violation_count+1 where id = #{id}")
    void vioStu(Integer id, Integer score);

    @Select("select c.name clazzName, count(*) cnt" +
            " from student s" +
            " left join clazz c on s.clazz_id = c.id" +
            " group by c.name")
    List<StuClazz> stuClazz();

    /**
     * 学员学历统计
     */
    @Select("select case degree" +
            "    when 1 then '初中'" +
            "    when 2 then '高中'" +
            "    when 3 then '大专'" +
            "    when 4 then '本科'" +
            "    when 5 then '硕士'" +
            "    else '博士'" +
            " end name,count(*) value" +
            " from student group by degree;")
    List<EmpGender> stuDegree();
}
